Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Chapter 27
Using the Oracle Optimizer

During the execution of SQL statements, Oracle chooses an execution plan by which these statements are executed. The execution plan is determined by the Oracle optimizer by using the optimization approach specified in the initialization parameters. The optimization approach can be overridden by the use of hints, as detailed in Chapter 30, “Using Hints.”

The effectiveness of the execution plan depends mainly on the optimization method chosen. This optimization method can consist of either a rule-based or cost-based approach. Which approach you take depends on both your application and your data.

How the Optimizer Works

To understand how the optimizer optimizes your SQL statements, it is useful to first look at how the optimizer works. When an SQL statement is parsed and passed off to the optimizer, the following occurs:

1.  The SQL statement is analyzed and evaluated. In this first stage, the SQL statement is essentially checked by the optimizer.
2.  The SQL statement is modified by the optimizer. If the statement is complex, the optimizer may change the statement to be more effectively processed, if necessary.
3.  View merging is performed. If the statement is accessing a view, the Oracle optimizer sometimes merges the query in the statement with a query in the view before optimization.
4.  The choice of optimization method is made. The optimizer chooses between a cost-based and rule-based approach, based on the amount of analysis data available for the object and also on any hints.
5.  The access path is chosen. The optimizer chooses one or more access paths to each table referenced in the SQL statement.
6.  The join order is chosen. If more than one join is done in the SQL statement, Oracle chooses the most appropriate order in which the joins will occur.
7.  The join operations are chosen. The optimizer chooses the most appropriate operations to use to perform the joins.

As you can see, the process through which the optimizer chooses the most optimal execution plan for the SQL statement is quite complex. But it is in this fashion that the best execution plan is usually chosen.

I could go into detail here about how the Oracle optimizer expands statements at the lowest level, but that is getting out of the scope of this book. It is enough to know that the optimizer breaks the statements into their constituent components and determines their individual costs.

How To Specify an Optimization Mode

The optimization mode (cost-based or rule-based) can be chosen using either of the following methods:

  The OPTIMIZATION_MODE initialization parameter
  The OPTIMIZER_GOAL parameter of the ALTER SESSION command

When specifying the optimization method with either the initialization file or the ALTER SESSION command, you can specify the following options:


Option Description

CHOOSE This option allows the Oracle optimizer to choose an optimization mode based on the availability of statistics on a particular table, cluster, or index. If statistics are available for any of the tables accessed in the SQL statement, the cost-based approach is used with the goal of best throughput. If none of the tables has statistics available, the rule-based approach is used; the rule-based approach is also the default if no optimization approach is specified.
RULE This option causes the Oracle optimizer to always use the rule-based optimization approach, regardless of any statistics that may have been gathered for the tables being accessed.
ALL_ROWS This option causes the optimizer to used the cost-based approach on all SQL statements, even if there are no statistics available for the tables being accessed. This approach has the goal of best throughput, with the least amount of system resources being used.
FIRST_ROWS This option causes the optimizer to use the cost-based approach on all SQL statements, even if no statistics are available for the tables being accessed. This approach has the goal of best response time.


NOTE:  If the cost-based optimization approach is used and no internal statistics are available for a table that is being accessed, other information (such as the number of data blocks in the table) is used to estimate the cost of various operations.

Optimization Methods

In general, the cost-based approach is the recommended approach. In most cases, the cost-based approach determines an execution plan that is as good or better than the rule-based approach. However, if you have manually tuned your SQL statements, you may get better performance with rule-based optimization than cost-based optimization.

The rule-based approach can be useful if you are moving a highly tuned application from an older version of Oracle that has been using the rule-based approach. This and a lack of statistics may cause rule-based optimization to be more efficient than the cost-based approach. However, as you gather statistics on your database, you may want to migrate to cost-based optimization.

The following sections examine both approaches. Hints can also be very useful in optimizing the execution of your SQL statements; hints are discussed in Chapter 30, “Using Hints.”


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.